﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Dmf;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Reflection;


namespace SQLServerDAL
{
  public class SQLHelper
  {

    //class SQLHelper
    //{
    #region 通用方法
    // 数据连接池  
    private SqlConnection con;
    /// <summary>  
    /// 返回数据库连接字符串  
    /// </summary>  
    /// <returns></returns>  
    public static String GetSqlConnection()
    {
      string conn;

      conn = @"Data Source=hds-040.hichina.com; Initial Catalog=hds0400180_db; User ID=hds0400180; Password=SZtaihexing2008;";
      //conn = @"Data Source=.;Initial Catalog=TaobaoProjectDB;Integrated Security=True";
      //conn = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\lando\Desktop\HuangDiHe\Project\TaoBaoProject\UI\App_Data\TaoBaoAnalyseDB.mdf;Integrated Security=True;User Instance=True";

      return conn;
    }
    #endregion
    #region 执行sql字符串
    /// <summary>  
    /// 执行不带参数的SQL语句  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public static int ExecuteSql(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = Sqlstr;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        return 1;
      }
    }
    /// <summary>  
    /// 执行带参数的SQL语句  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <param name="param">参数对象数组</param>  
    /// <returns></returns>  
    public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = Sqlstr;
        cmd.Parameters.AddRange(param);
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        return 1;
      }
    }
    /// <summary>  
    /// 返回DataReader  
    /// </summary>  
    /// <param name="Sqlstr"></param>  
    /// <returns></returns>  
    public static SqlDataReader ExecuteReader(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
      try
      {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = Sqlstr;
        conn.Open();
        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
      }
      catch //(Exception ex)  
      {
        return null;
      }
    }
    public static SqlDataReader ExecuteReader(String Sqlstr, SqlParameter[] paras)
    {
      String ConnStr = GetSqlConnection();
      SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
      try
      {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = Sqlstr;
        cmd.Parameters.AddRange(paras);
        conn.Open();
        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
      }
      catch //(Exception ex)  
      {
        return null;
      }
    }
    /// <summary>  
    /// 执行SQL语句并返回数据表  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public static DataTable ExecuteDt(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
        DataTable dt = new DataTable();
        conn.Open();
        da.Fill(dt);
        conn.Close();
        return dt;
      }
    }
    /// <summary>  
    /// 执行带参数SQL语句并返回数据表  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <param name="param">参数对象数组</param>  
    /// <returns></returns>  
    public static DataTable ExecuteDt(String Sqlstr, SqlParameter[] param)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
        da.SelectCommand.Parameters.AddRange(param);
        DataTable dt = new DataTable();
        conn.Open();
        da.Fill(dt);
        conn.Close();
        return dt;
      }
    }
    /// <summary>  
    /// 执行SQL语句并返回DataSet  
    /// </summary>  
    /// <param name="Sqlstr">SQL语句</param>  
    /// <returns></returns>  
    public static DataSet ExecuteDs(String Sqlstr)
    {
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
        DataSet ds = new DataSet();
        conn.Open();
        da.Fill(ds);
        conn.Close();
        return ds;
      }
    }

    public static int ExecuteScalar(string Sqlstr, SqlParameter[] param)
    {
      object result = 0;
      String ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = Sqlstr;
        cmd.Parameters.AddRange(param);
        result = cmd.ExecuteScalar();
        conn.Close();
      }

      return result == null ? 0 : 1;
    }

    public static int ExecuteScalar(string Sqlstr)
    {
      int result = 0;
      string ConnStr = GetSqlConnection();
      using (SqlConnection conn = new SqlConnection(ConnStr))
      {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = Sqlstr;
        result = (int)cmd.ExecuteScalar();
      }
      return result;
    }
    #endregion
    #region 操作存储过程
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <returns>存储过程的返回值</returns>  
    public int RunProc(string procName)
    {
      SqlCommand cmd = CreateCommand(procName, null);
      cmd.ExecuteNonQuery();
      this.Close();
      return (int)cmd.Parameters["ReturnValue"].Value;
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <returns>存储过程的返回值</returns>  
    public int RunProc(string procName, SqlParameter[] prams)
    {
      SqlCommand cmd = CreateCommand(procName, prams);
      cmd.ExecuteNonQuery();
      this.Close();
      return (int)cmd.Parameters[0].Value;
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="dataReader">结果集</param>  
    public void RunProc(string procName, out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(procName, null);
      dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    /// <summary>  
    /// 运行存储过程(已重载)  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <param name="dataReader">结果集</param>  
    public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
    {
      SqlCommand cmd = CreateCommand(procName, prams);
      dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
    public DataSet RunProc(string procName, SqlParameter[] prams, string tableName)
    {
      DataSet ds = new DataSet();
      SqlCommand cmd = CreateCommand(procName, prams);
      SqlDataAdapter da = new SqlDataAdapter();
      da.SelectCommand = cmd;
      da.Fill(ds, tableName);
      return ds;
      //dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

    }
    /// <summary>  
    /// 创建Command对象用于访问存储过程  
    /// </summary>  
    /// <param name="procName">存储过程的名字</param>  
    /// <param name="prams">存储过程的输入参数列表</param>  
    /// <returns>Command对象</returns>  
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
      // 确定连接是打开的  
      Open();
      //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );  
      SqlCommand cmd = new SqlCommand(procName, con);
      cmd.CommandType = CommandType.StoredProcedure;
      // 添加存储过程的输入参数列表  
      if (prams != null)
      {
        foreach (SqlParameter parameter in prams)
          cmd.Parameters.Add(parameter);
      }
      // 返回Command对象  
      return cmd;
    }
    /// <summary>  
    /// 创建输入参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <param name="Value">参数值</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
      return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }
    /// <summary>  
    /// 创建输出参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
      return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }
    /// <summary>  
    /// 创建存储过程参数  
    /// </summary>  
    /// <param name="ParamName">参数名</param>  
    /// <param name="DbType">参数类型</param>  
    /// <param name="Size">参数大小</param>  
    /// <param name="Direction">参数的方向(输入/输出)</param>  
    /// <param name="Value">参数值</param>  
    /// <returns>新参数对象</returns>  
    public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
      SqlParameter param;
      if (Size > 0)
      {
        param = new SqlParameter(ParamName, DbType, Size);
      }
      else
      {
        param = new SqlParameter(ParamName, DbType);
      }
      param.Direction = Direction;
      if (!(Direction == ParameterDirection.Output && Value == null))
      {
        param.Value = Value;
      }
      return param;
    }
    #endregion
    #region 数据库连接和关闭
    /// <summary>  
    /// 打开连接池  
    /// </summary>  
    private void Open()
    {
      // 打开连接池  
      if (con == null)
      {
        //这里不仅需要using System.Configuration;还要在引用目录里添加  
        con = new SqlConnection(GetSqlConnection());
        con.Open();
      }
    }
    /// <summary>  
    /// 关闭连接池  
    /// </summary>  
    public void Close()
    {
      if (con != null)
        con.Close();
    }
    /// <summary>  
    /// 释放连接池  
    /// </summary>  
    public void Dispose()
    {
      // 确定连接已关闭  
      if (con != null)
      {
        con.Dispose();
        con = null;
      }
    }
    #endregion

    #region Back Database
    public static bool BackDB()
    {
      string connStr = GetSqlConnection();
      bool isOk = false;

      string sqlBKSavePath = AppDomain.CurrentDomain.BaseDirectory + "UploadSqls\\";

      using (SqlConnection conn = new SqlConnection(connStr))
      {

        #region

        //SqlCommand bkCmd = new SqlCommand();
        //bkCmd.CommandType = CommandType.Text;
        //bkCmd.Connection = conn;
        //bkCmd.CommandText = @"backup database master to disk='C:\DMTMigration' with init";
        //try
        //{
        //    conn.Open();
        //    bkCmd.ExecuteNonQuery();
        //    isOk = true;
        //}
        //catch (Exception ex)
        //{
        //    isOk = false;
        //}
        #endregion

        Server server = new Server(new ServerConnection(conn));
        //FileInfo file = new FileInfo("C:\\myscript.sql");
        //string script = file.OpenText().ReadToEnd();
        try
        {
          Database database = server.Databases[@"C:\Users\lando\Desktop\HuangDiHe\Project\TaoBaoProject\UI\App_Data\TaoBaoAnalyseDB.mdf"];
          Scripter scripter = new Scripter(server);
          scripter.Options.ScriptDrops = false;
          scripter.Options.WithDependencies = true;
          scripter.Options.ScriptData = true;
          scripter.Options.AppendToFile = true;
          Urn[] smoObjects = new Urn[1];
          string backName = "Backup(" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ").sql";

          StreamWriter write = File.CreateText(sqlBKSavePath + backName);
          foreach (Table table in database.Tables)
          {
            smoObjects[0] = table.Urn;
            if (!table.IsSystemObject)
            {
              foreach (string s in scripter.EnumScript(smoObjects))
              {
                write.WriteLine(s);
              }
            }
          }
          isOk = true;
          write.Close();
        }
        catch (Exception ex)
        {
          isOk = false;
        }

      }
      return isOk;


    }

    //public static bool BackupDB(string backupDestinationFilePath)
    //{
    //  try
    //  {
    //    #region

    //    //SqlConnection conn = new SqlConnection(GetSqlConnection());
    //    //Server sqlServer = new Server(new ServerConnection(conn));
    //    //DatabaseCollection allDatabases = sqlServer.Databases;
    //    //Backup backup = new Backup();
    //    ////Set type of backup to be performed to database
    //    //backup.Action = BackupActionType.Database;
    //    //backup.BackupSetDescription = "BackupDataBase description";
    //    ////Set the name used to identify a particular backup set.
    //    //backup.BackupSetName = "Backup";
    //    ////specify the name of the database to back up
    //    //backup.Database = allDatabases[0].Name;
    //    ////Set up the backup device to use filesystem.
    //    //BackupDeviceItem deviceItem = new BackupDeviceItem(
    //    //                                backupDestinationFilePath,
    //    //                                DeviceType.File);
    //    //backup.Devices.Add(deviceItem);

    //    ////// Setup a new connection to the data server
    //    ////ServerConnection connection = new ServerConnection(@"SERVER_NAME");
    //    ////// Log in using SQL authentication
    //    ////connection.LoginSecure = false;
    //    ////connection.Login = "testuser";
    //    ////connection.Password = "testuser";

    //    ////Server sqlServer = new Server(connection);
    //    ////Initialize devices associated with a backup operation.
    //    //backup.Initialize = true;
    //    //backup.Checksum = true;
    //    ////Set it to true to have the process continue even
    //    ////after checksum error.
    //    //backup.ContinueAfterError = true;
    //    ////Set the backup expiry date.
    //    //backup.ExpirationDate = DateTime.Now.AddDays(3);
    //    ////truncate the database log as part of the backup operation.
    //    //backup.LogTruncation = BackupTruncateLogType.Truncate;
    //    ////start the back up operation
    //    //backup.SqlBackup(sqlServer);
    //    #endregion
    //    #region Restore Code
    //    //string connStr = GetSqlConnection();
    //    //bool isOk = false;
    //    //using (SqlConnection conn = new SqlConnection(connStr))
    //    //{
    //    //    Server server = new Server(new ServerConnection(conn));
    //    //    FileInfo file = new FileInfo(backupDestinationFilePath);
    //    //    string script = file.OpenText().ReadToEnd();

    //    //    server.ConnectionContext.ExecuteNonQuery(script);
    //    //}
    //    #endregion

    //    return true;

    //  }
    //  catch (Exception ex)
    //  {
    //    return false;
    //  }
    //}

    //public static bool RestoreDB(string backUpFilePath, string databaseName)
    //{
    //  try
    //  {
    //    //C:\DB\TaoBaoAnalyseDB.mdf
    //    Restore restore = new Restore();
    //    //Set type of backup to be performed to database
    //    restore.Database = databaseName;
    //    restore.Action = RestoreActionType.Database;
    //    //Set up the backup device to use filesystem.         
    //    restore.Devices.AddDevice(backUpFilePath, DeviceType.File);
    //    //set ReplaceDatabase = true to create new database
    //    //regardless of the existence of specified database
    //    restore.ReplaceDatabase = false;
    //    //If you have a differential or log restore to be followed,
    //    //you would specify NoRecovery = true
    //    restore.NoRecovery = false;
    //    //if you want to restore to a different location, specify
    //    //the logical and physical file names
    //    restore.RelocateFiles.Add(new RelocateFile("Test", @"C:\Temp\Test.mdf"));
    //    restore.RelocateFiles.Add(new RelocateFile("Test_Log", @"C:\Temp\Test_Log.ldf"));
    //    //            ServerConnection connection = new
    //    //ServerConnection(@"SERVER_NAME");
    //    //            //my SQL user doesnt have sufficient permissions,
    //    //            //so i am using my windows account
    //    //            connection.LoginSecure = true;
    //    SqlConnection conn = new SqlConnection(GetSqlConnection());
    //    Server sqlServer = new Server(new ServerConnection(conn));

    //    restore.SqlRestore(sqlServer);
    //    return true;

    //  }
    //  catch (Exception ex)
    //  {
    //    return false;
    //  }
    //}

    #endregion
  }
}


//}
